import datetime
import json
import sys
from decimal import Decimal

import pymysql
import pymysql.converters
from PySide6 import QtCore, QtGui
from PySide6.QtGui import QAction, QKeySequence, QShortcut, QIcon, QPalette
from PySide6.QtWidgets import QMainWindow, QTableWidget, QTableWidgetItem, QHeaderView, QAbstractItemView, QStatusBar, \
    QMenu, QApplication, QWidget, QMessageBox, QToolButton, QListWidget, QListWidgetItem
from PySide6.QtCore import Qt, QPoint, Slot, QEvent

from controllers.loading import LoadingMask
from global_variables import GlobalVariables
from util import common
from util.common import hum_convert


class DataListFillData(QMainWindow):
    def __init__(self, app):
        super().__init__()
        self.app = app
        self.ui = app.ui
        self.mysqldb = GlobalVariables.DATABASE_OBJ
        self.__data_list_filter__ = self.app.data_list_filter
        self.previous_row: int = -1
        self.previous_row_content_is_changed: bool = False
        self.init()

    def init(self):
        # 添加事件回调
        self.ui.data_list.itemChanged.connect(self.data_list_item_changed)
        self.ui.data_list.currentCellChanged.connect(self.data_list_current_cell_changed)
        self.ui.data_list.horizontalHeader().sectionClicked \
            .connect(lambda index: self.header_clicked_order_by(self.ui.data_list, index))
        # 右键菜单
        self.ui.data_list.setContextMenuPolicy(Qt.CustomContextMenu)
        self.ui.data_list.customContextMenuRequested.connect(self.show_menu)

        self.add_shortcut()

    def add_shortcut(self):
        if sys.platform == 'win32':

            # 添加刷新快捷键
            shortcut = QShortcut(QKeySequence("Alt+R"), self.ui.data_list)
            shortcut.activated.connect(self.refresh_items)

            # 添加新增行快捷键
            addShortcut = QShortcut(QKeySequence("Ctrl+D"), self.ui.data_list)
            addShortcut.activated.connect(self.add_row_items_clicked)

            # 添加删除快捷键
            deleteShortcut = QShortcut(QKeySequence(QtCore.Qt.Key.Key_Delete), self.ui.data_list)
            deleteShortcut.activated.connect(self.del_items_clicked)
        elif sys.platform == 'darwin':
            # 添加刷新快捷键
            shortcut = QShortcut(QKeySequence("Ctrl+R"), self.ui.data_list)
            shortcut.activated.connect(self.refresh_items)

            # 添加新增行快捷键
            addShortcut = QShortcut(QKeySequence("Ctrl+D"), self.ui.data_list)
            addShortcut.activated.connect(self.add_row_items_clicked)

            # 添加删除快捷键
            deleteShortcut = QShortcut(QKeySequence("Ctrl+Backspace"), self.ui.data_list)
            deleteShortcut.activated.connect(self.del_items_clicked)

    def data_list_clear(self):
        self.data_list_fill_data_v2(obj=self.ui.data_list, columns=[], data=[])
        self.ui.filter_widgets: QWidget
        self.ui.filter_widgets.hide()

    def data_list_fill_data_v2(self, obj: QTableWidget, columns: list, data: list):
        # 清空数据表
        obj.clearContents()
        obj.setRowCount(0)
        GlobalVariables.DATA_LIST_UNIQUE_FIELD_MAP = {}
        unique_field = GlobalVariables.DATA_LIST_UNIQUE_FIELD

        # 设置隔行变色
        obj.setAlternatingRowColors(True)
        obj.setPalette(QPalette(Qt.gray))

        # 设置列数
        obj.setColumnCount(len(columns))
        # 设置自适应宽度
        obj.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents)
        # 设置列最大宽度
        obj.horizontalHeader().setMaximumSectionSize(200)
        # 设置列最小宽度
        obj.horizontalHeader().setMinimumSectionSize(30)
        # 设置表格为整行选择
        obj.setSelectionBehavior(QAbstractItemView.SelectRows)

        map_k_column = {}
        for k in range(len(columns)):
            map_k_column[columns[k]] = k
            obj.setHorizontalHeaderItem(k, QTableWidgetItem(columns[k]))

        GlobalVariables.DATA_LIST_IS_INSERT = True
        for d in data:
            # print(d)
            self.add_item(obj, unique_field, d, map_k_column)
        GlobalVariables.DATA_LIST_IS_INSERT = False

    # 添加单元格
    def add_item(self, obj, unique_field, d, map_k_column):
        row_count = obj.rowCount() + 1
        obj.setRowCount(row_count)
        if unique_field is not None:
            GlobalVariables.DATA_LIST_UNIQUE_FIELD_MAP[row_count - 1] = d[unique_field]
        for column in map_k_column:
            if isinstance(d[column], float):
                content = str(d[column])
            elif isinstance(d[column], int):
                content = str(d[column])
            elif isinstance(d[column], str):
                content = d[column]
            elif d[column] is None:
                content = 'NULL'
            elif isinstance(d[column], bytes):
                content = d[column].decode('utf-8')
            elif isinstance(d[column], Decimal):
                content = str(d[column])
            elif isinstance(d[column], datetime.datetime):
                content = d[column].strftime("%Y-%m-%d %H:%M:%S")
            elif isinstance(d[column], datetime.timedelta):
                content = str(d[column])
            elif isinstance(d[column], datetime.date):
                content = d[column].strftime("%Y-%m-%d")
            else:
                content = ''
                print(d[column], type(d[column]), map_k_column[column])
            obj.setItem(row_count - 1, map_k_column[column], QTableWidgetItem(content))

    def set_global(self, database: str, table_name: str):
        GlobalVariables.DATA_LIST_MAP_FIELD_TYPE = {}
        for i in self.mysqldb.table_columns(database, table_name):
            GlobalVariables.DATA_LIST_MAP_FIELD_TYPE[i['COLUMN_NAME']] = i['DATA_TYPE']

    def first_data_list_fill_data(self, table_name: str = None):
        limit = int(self.ui.data_page_limit.currentText())
        page_num = GlobalVariables.DATA_LIST_PAGE_NUM
        self.data_list_fill_data(table_name=table_name, limit=limit, page_num=page_num)

    def data_list_fill_data(self, table_name: str, where: list = [], page_num: int = 1, limit: int = 2000,
                            order_by: dict = {}):
        self.ui.data_list.setProperty("table_name", table_name)

        LoadingMask.showToast(self.ui)
        database = self.ui.database_select.currentText()

        offset = (page_num - 1) * limit
        # 数据表数据
        count, list_data = self.mysqldb.table_content(
            table_name=table_name,
            where=where,
            order_by=order_by,
            offset=offset,
            limit=limit)


        # 上一页按钮是否启用
        if page_num <= 1:
            self.ui.page_prev.setDisabled(True)
        else:
            self.ui.page_prev.setEnabled(True)
        # 下一页按钮是否启用
        if (page_num - 1 + 1) * limit >= count:
            self.ui.page_next.setDisabled(True)
        else:
            self.ui.page_next.setEnabled(True)

        # 查询主键
        list_column_status = self.mysqldb.show_columns_from_table(table_name=table_name)
        self.set_global(database, table_name)
        GlobalVariables.DATA_LIST_HEADER_MAP = []
        GlobalVariables.DATA_LIST_UNIQUE_FIELD = None
        for c in list_column_status:
            GlobalVariables.DATA_LIST_HEADER_MAP.append(c['Field'])
            if c['Key'] in ['PRI', 'UNI'] and GlobalVariables.DATA_LIST_UNIQUE_FIELD is None:
                GlobalVariables.DATA_LIST_UNIQUE_FIELD = c['Field']

        self.__data_list_filter__.set_filter_columns()

        columns = GlobalVariables.DATA_LIST_HEADER_MAP
        self.previous_row_content_is_changed = False
        self.previous_row = -1
        self.data_list_fill_data_v2(obj=self.ui.data_list, columns=columns, data=list_data)
        self.ui.data_list.itemSelectionChanged.connect(self.data_list_item_selection_changed)

    def header_clicked_order_by(self, obj: QTableWidget, column_index: int):
        if column_index >= 0:
            current_item: QTableWidgetItem = obj.horizontalHeaderItem(column_index)
            icon: QIcon = current_item.icon()

            if icon.isNull():
                desc: str = 'ASC'
            elif current_item.desc == 'DESC':
                desc: str = None
            elif current_item.desc == 'ASC':
                desc: str = 'DESC'
            else:
                desc: str = None
            current_item.desc = desc

        map_field_desc = {}
        for index in range(obj.columnCount()):
            item = obj.horizontalHeaderItem(index)
            if hasattr(item, 'desc'):
                map_field_desc[index] = item.desc
        self.__data_list_filter__.filter_value(obj)
        for index, desc in map_field_desc.items():
            item: QTableWidgetItem = obj.horizontalHeaderItem(index)
            item.desc = desc
            if item.desc == 'ASC':
                icon = QIcon(GlobalVariables.PROJECT_BASE_PATH + 'resource/asset/triangle-up.png')
            elif item.desc == 'DESC':
                icon = QIcon(GlobalVariables.PROJECT_BASE_PATH + 'resource/asset/triangle-down.png')
            else:
                icon = QIcon(None)
            item.setIcon(icon)

    def data_list_item_selection_changed(self):
        self.ui.field_list.blockSignals(True)
        row_count = 0
        row_list = []
        for i in self.ui.data_list.selectedItems():
            if i.row() not in row_list:
                row_list.append(i.row())
                row_count += 1
        statusBar: QStatusBar = self.ui.statusbar
        statusBar.showMessage(f'{row_count} rows selected', 0)
        # statusBar.addWidget()
        self.ui.field_list.blockSignals(False)

    # 单元格更新
    def data_list_item_changed(self, item: QTableWidgetItem):
        if GlobalVariables.DATA_LIST_IS_INSERT:
            return

        table_name = self.get_table_name()
        if table_name is None:
            QMessageBox.information(self, "错误", "数据表未选中")
            return

        column = GlobalVariables.DATA_LIST_HEADER_MAP.index(GlobalVariables.DATA_LIST_UNIQUE_FIELD)
        unique_field_item = self.ui.data_list.item(item.row(), column)
        text = unique_field_item.text()
        if text == 'NULL':
            return

        res = self.mysqldb.record_update(
            table_name=table_name,
            where={
                GlobalVariables.DATA_LIST_UNIQUE_FIELD: GlobalVariables.DATA_LIST_UNIQUE_FIELD_MAP[item.row()]
            },
            value={
                GlobalVariables.DATA_LIST_HEADER_MAP[item.column()]: item.text()
            }
        )
        self.__data_list_filter__.filter_value_return_pressed()

    def show_menu(self, point: QPoint):
        menu = QMenu(self.ui.data_list)
        self.ui.data_list: QTableWidget
        point.setY(point.y() + self.ui.filter_widgets.height() - 20)
        point.setX(point.x() + self.ui.data_list.verticalHeader().width())

        table_name = self.get_table_name()
        if table_name is None:
            return
        common.add_action(menu, '刷新', self.refresh_items)
        common.add_action(menu, '新增', self.add_row_items_clicked)

        selectedItems = self.ui.data_list.selectedItems()
        if len(selectedItems) != 0:
            common.add_action(menu, '删除', self.del_items_clicked)
            common.add_action(menu, '复制为Insert语句', self.items_copy_insert_sql)
            common.add_action(menu, '复制为Json', self.items_copy_json)

        desk_point = self.ui.data_list.mapToGlobal(point)
        menu.exec_(desk_point)

    # 刷新表格
    def refresh_items(self):
        self.__data_list_filter__.filter_value_return_pressed()

    # 添加一行数据
    def add_row_items_clicked(self):
        self.ui.data_list.itemChanged.disconnect(self.data_list_item_changed)
        selectedItems = self.ui.data_list.selectedItems()
        self.ui.data_list: QTableWidget
        # 如果没有选中行，则在最后一行添加一行
        map_insert_data = {}
        if len(selectedItems) == 0:
            for field, data_type in GlobalVariables.DATA_LIST_MAP_FIELD_TYPE.items():
                if data_type in ['tinyint', 'smallint', 'mediumint', 'int',
                                 'integer', 'bigint', 'bit', 'real',
                                 'double', 'float', 'decimal', ]:
                    map_insert_data[field] = 0
                else:
                    map_insert_data[field] = ''

        # 如果有选中行，则复制选中行
        else:
            for _, item in enumerate(selectedItems):
                field = GlobalVariables.DATA_LIST_HEADER_MAP[item.column()]
                map_insert_data[field] = item.text()
                item.setSelected(False)

        # 处理主键
        map_insert_data[GlobalVariables.DATA_LIST_UNIQUE_FIELD] = None
        columns = GlobalVariables.DATA_LIST_HEADER_MAP
        obj: QTableWidget = self.ui.data_list
        map_k_column = {}
        for k in range(len(columns)):
            map_k_column[columns[k]] = k
        self.add_item(obj, None, map_insert_data, map_k_column)
        obj.selectRow(obj.rowCount() - 1)
        self.ui.data_list.itemChanged.connect(self.data_list_item_changed)
        self.previous_row_content_is_changed = True
        self.previous_row = obj.rowCount() - 1

    # 删除选中元素
    def del_items_clicked(self):
        selectedItems = self.ui.data_list.selectedItems()
        if len(selectedItems) == 0:
            return
        if GlobalVariables.DATA_LIST_UNIQUE_FIELD is None:
            return

        table_name = self.get_table_name()
        if table_name is None:
            return

        rows = []
        for item in selectedItems:
            row = item.row()
            if GlobalVariables.DATA_LIST_UNIQUE_FIELD_MAP.get(row) is None:
                continue
            if row not in rows:
                rows.append(row)

        if len(rows) == 0:
            self.__data_list_filter__.filter_value_return_pressed()
            return

        result = QMessageBox.question(self, '确认删除', f'确认是否删除行')
        if result != QMessageBox.StandardButton.Yes:
            return

        for row in rows:
            res = self.mysqldb.delete_record(
                table_name=table_name,
                where={
                    GlobalVariables.DATA_LIST_UNIQUE_FIELD: GlobalVariables.DATA_LIST_UNIQUE_FIELD_MAP[row]
                }
            )
        self.__data_list_filter__.filter_value_return_pressed()

    def items_copy_json(self):
        selectedItems = self.ui.data_list.selectedItems()
        map_row = {}
        for item in selectedItems:
            if item.row() in map_row:
                map_row[item.row()][GlobalVariables.DATA_LIST_HEADER_MAP[item.column()]] = item.text()
            else:
                map_row[item.row()] = {
                    GlobalVariables.DATA_LIST_HEADER_MAP[item.column()]: item.text()
                }
        json_str = json.dumps(list(map_row.values()), ensure_ascii=False, indent=4)
        clipboard = QApplication.clipboard()
        clipboard.setText(json_str)

    # 复制为Insert语句
    def items_copy_insert_sql(self):
        table_name = self.get_table_name()
        if table_name is None:
            return
        selectedItems = self.ui.data_list.selectedItems()
        map_row = {}
        for item in selectedItems:
            if item.row() in map_row:
                map_row[item.row()][GlobalVariables.DATA_LIST_HEADER_MAP[item.column()]] = item.text()
            else:
                map_row[item.row()] = {
                    GlobalVariables.DATA_LIST_HEADER_MAP[item.column()]: item.text()
                }
        list_sql = []
        for i in map_row:
            row = map_row[i]
            values = [pymysql.converters.escape_string(i) for i in list(row.values())]
            sql = f'insert into `{table_name}` (`' + \
                  "`, `".join(list(row.keys())) + "`) values ('" \
                  + "', '".join(values) + "');"
            list_sql.append(sql)
        clipboard = QApplication.clipboard()
        clipboard.setText("\n".join(list_sql))

    def data_list_current_cell_changed(self, current_row, current_column, previous_row, previous_column):
        # 如果 记录行 为-1，记录当前行，return
        if self.previous_row == -1:
            self.previous_row = current_row
            return
        # 如果当前行与记录行不一致，发起更新请求，更新行的行号为记录行;更新记录行为当前行
        if self.previous_row != current_row:
            if self.previous_row_content_is_changed:
                self.data_update(self.previous_row)
                self.refresh_items()
                self.previous_row = current_row
                self.previous_row_content_is_changed = False
            else:
                self.previous_row = current_row

    def data_update(self, row: int):
        table: QTableWidget = self.ui.data_list
        table_name = self.get_table_name()
        if table_name is None:
            return

        # 需要更新或者新增的数据
        tmp_data = {}
        for column in range(table.columnCount()):
            item = self.ui.data_list.item(row, column)
            tmp_data[GlobalVariables.DATA_LIST_HEADER_MAP[item.column()]] = item.text()
        # 数据中去除唯一键，待更新的数据中不需要更新唯一字段数据
        tmp_data.pop(GlobalVariables.DATA_LIST_UNIQUE_FIELD)

        column = GlobalVariables.DATA_LIST_HEADER_MAP.index(GlobalVariables.DATA_LIST_UNIQUE_FIELD)
        unique_field_item = self.ui.data_list.item(row, column)
        text = unique_field_item.text()
        if text == 'NULL':
            try:
                res = self.mysqldb.record_insert(
                    table_name=table_name,
                    value=tmp_data
                )
            except pymysql.err.IntegrityError as e:
                code, msg = e.args
                QMessageBox.critical(self, '新增行错误', str(code) + "\t" + msg)
            except pymysql.err.OperationalError as e:
                code, msg = e.args
                QMessageBox.critical(self, '新增行错误', str(code) + "\t" + msg)
            return
        try:

            res = self.mysqldb.record_update(
                table_name=table_name,
                where={
                    GlobalVariables.DATA_LIST_UNIQUE_FIELD: GlobalVariables.DATA_LIST_UNIQUE_FIELD_MAP[item.row()]
                },
                value=tmp_data
            )
        except pymysql.err.OperationalError as e:
            code, msg = e.args
            QMessageBox.critical(self, '更新行错误', str(code) + "\t" + msg)

    def data_list_clicked(self):
        # 如果焦点切换，可以触发添加数据的操作，现在没有
        pass

    def get_table_name(self):
        item = self.ui.table_list.currentItem()
        if isinstance(item, QListWidgetItem):
            return item.text()
        return None
